<?php

/**
 * report actions.
 *
 * @package    student_manager
 * @subpackage report
 * @author     Your name here
 * @version    SVN: $Id: actions.class.php 2288 2006-10-02 15:22:13Z fabien $
 */
class reportActions extends sfActions
{

public function executeProject(){    
       $this->getResponse()->setTitle('WMS-Report-Project');
       include('filter.php'); 
       
       $result = null;
       $title = null;
	if ($this->getRequest()->isMethod('post'))
        {
            
            $allprojects = isset($filters['allprojects'])? 1 : 0;
            $projectId = $filters['projectId'];            
            $type = $filters['type'];
            
            $this->filters = $filters;
            //TALLY OUT
            if ($type == 0)
            {
                $title = "TALLY OUT TO PROJECT";
                //chuan bi de tim kiem
                $fromDate = "TO-". sprintf("%02d",$fromyear % 100)."-". sprintf("%02d", $frommonth). "-000";
                $toDate= "TO-". sprintf("%02d",$toyear % 100)."-". sprintf("%02d", $tomonth). "-999";                

                if ($allprojects)
                {
                    //tim kiem
                    $c = new Criteria();    
                    $c->add(TallyOutPeer::STATUS  , CommonValues::$gTLStatus['key']['Published']);
                    $c->add(TallyOutPeer::TYPE  , CommonValues::$gTallyOutType['key']['Project']);                
                    $c->add(BOMPeer::STATUS, CommonValues::$gBomStatus['key']['In progress']);
                    $c->addOr(BOMPeer::STATUS, CommonValues::$gBomStatus['key']['Completed']);
                    $c->addJoin(TallyOutPeer::TALLYNO, BOMPeer::ID, Criteria::LEFT_JOIN);
                    $c->add(TallyOutPeer::TALLYOUTCODE, $fromDate, Criteria::GREATER_EQUAL);
                    $c->addAnd(TallyOutPeer::TALLYOUTCODE, $toDate, Criteria::LESS_EQUAL);
                    $TallyoutList  = TallyOutPeer::doSelect($c);


                    $c = new Criteria();    
                    $c->add(TallyOutPeer::STATUS  , CommonValues::$gTLStatus['key']['Published']);
                    $c->add(TallyOutPeer::TYPE  , CommonValues::$gTallyOutType['key']['Production']);
                    $c->add(TallyOutPeer::TALLYOUTCODE, $fromDate, Criteria::GREATER_EQUAL);
                    $c->addAnd(TallyOutPeer::TALLYOUTCODE, $toDate, Criteria::LESS_EQUAL);
                    $TallyoutList  = array_merge($TallyoutList, TallyOutPeer::doSelect($c));
                }
                else 
                {
                    //tim kiem
                    $c = new Criteria();    
                    $c->add(TallyOutPeer::STATUS   , CommonValues::$gTLStatus['key']['Published']);
                    $c->add(TallyOutPeer::TYPE  , CommonValues::$gTallyOutType['key']['Project']);
                    $c->add(TallyOutPeer::TALLYOUTCODE, $fromDate, Criteria::GREATER_EQUAL);
                    $c->addAnd(TallyOutPeer::TALLYOUTCODE, $toDate, Criteria::LESS_EQUAL);
                    $c->add(BOMPeer::PROJECT_ID, $projectId);
                    $c->add(BOMPeer::STATUS, CommonValues::$gBomStatus['key']['In progress']);
                    $c->addOr(BOMPeer::STATUS, CommonValues::$gBomStatus['key']['Completed']);
                    $c->addJoin(TallyOutPeer::TALLYNO, BOMPeer::ID, Criteria::LEFT_JOIN);
                    $TallyoutList  = TallyOutPeer::doSelect($c);   

                    $c = new Criteria();    
                    $c->add(TallyOutPeer::STATUS   , CommonValues::$gTLStatus['key']['Published']);
                    $c->add(TallyOutPeer::TYPE  , CommonValues::$gTallyOutType['key']['Production']);
                    $c->add(TallyOutPeer::TALLYOUTCODE, $fromDate, Criteria::GREATER_EQUAL);
                    $c->addAnd(TallyOutPeer::TALLYOUTCODE, $toDate, Criteria::LESS_EQUAL);
                    $c->add(TallyOutPeer::TALLYNO, $projectId);
                    $TallyoutList  = array_merge($TallyoutList, TallyOutPeer::doSelect($c));
                }

                $CompList= array();
                $count = count($TallyoutList);
                for ($i = 0; $i < $count; $i++)
                {
                    $c = new Criteria();
                    $c->add(ComponentsPeer::COMPONENTGROUP_ID, $TallyoutList[$i]->getComponentgroupId());
                    $compsList = ComponentsPeer::doSelect($c);
                    $subCount = count($compsList);
                    for ($j = 0; $j < $subCount; $j++)
                    {
                        if (isset($CompList[$compsList[$j]->getComponentId()]))
                            $CompList[$compsList[$j]->getComponentId()] += $compsList[$j]->getQty1();
                        else
                            $CompList[$compsList[$j]->getComponentId()] = $compsList[$j]->getQty1();
                    }
                }
            }
            //TALLY IN
            else if ($type == 1)
            {
                $title = "TALLY IN OF PROJECT";
		
		//chuan bi de tim kiem
                $fromDate = "TI-". sprintf("%02d",$fromyear % 100)."-". sprintf("%02d", $frommonth). "-000";
                $toDate= "TI-". sprintf("%02d",$toyear % 100)."-". sprintf("%02d", $tomonth). "-999";

                if ($allprojects){

                    //tim kiem
                    $c = new Criteria();
                    $c->add(TallyInPeer::STATUS, CommonValues::$gTLStatus['key']['Published']);
                    $c->add(TallyInPeer::TYPE, CommonValues::$gTallyInType['key']['Supplier']);
                    $c->add(TallyInPeer::TALLYINCODE, $fromDate, Criteria::GREATER_EQUAL);
                    $c->addAnd(TallyInPeer::TALLYINCODE, $toDate, Criteria::LESS_EQUAL);
                    $c->add(POPeer::COMPCATEGORY, CommonValues::$gCompCategory['key']['Main Component']);
                    $c->add(POPeer::APPROVALSTATUS, CommonValues::$gMRApprovalStatus['key']['Approved']);
                    $c->add(POPeer::RECEIVEDSTATUS, CommonValues::$gPOReceivedStatus['key']['Fully Received']);
                    $c->addOr(POPeer::RECEIVEDSTATUS, CommonValues::$gPOReceivedStatus['key']['Partially Received']);
                    $c->add(POPeer::PROJECT_ID, null, Criteria::ISNOTNULL);
                    $c->addJoin(TallyInPeer::TALLYNO, POPeer::ID, Criteria::LEFT_JOIN);
                    $TallyinList  = TallyInPeer::doSelect($c);
                }
                else 
                {
                    //tim kiem
                    $c = new Criteria();
                    $c->add(TallyInPeer::STATUS, CommonValues::$gTLStatus['key']['Published']);
                    $c->add(TallyInPeer::TYPE, CommonValues::$gTallyInType['key']['Supplier']);
                    $c->add(TallyInPeer::TALLYINCODE, $fromDate, Criteria::GREATER_EQUAL);
                    $c->addAnd(TallyInPeer::TALLYINCODE, $toDate, Criteria::LESS_EQUAL);
                    $c->add(POPeer::COMPCATEGORY, CommonValues::$gCompCategory['key']['Main Component']);
                    $c->add(POPeer::APPROVALSTATUS, CommonValues::$gMRApprovalStatus['key']['Approved']);
                    $c->add(POPeer::RECEIVEDSTATUS, CommonValues::$gPOReceivedStatus['key']['Fully Received']);
                    $c->addOr(POPeer::RECEIVEDSTATUS, CommonValues::$gPOReceivedStatus['key']['Partially Received']);
                    $c->add(POPeer::PROJECT_ID, $projectId);
                    $c->addJoin(TallyInPeer::TALLYNO, POPeer::ID, Criteria::LEFT_JOIN);
                    $TallyinList  = TallyInPeer::doSelect($c);
                }
		$CompList= array();
                $count = count($TallyinList);
                for ($i = 0; $i < $count; $i++)
                {
                    $c = new Criteria();
                    $c->add(ComponentsPeer::COMPONENTGROUP_ID, $TallyinList[$i]->getComponentgroupId());
                    $compsList = ComponentsPeer::doSelect($c);
                    $subCount = count($compsList);
                    for ($j = 0; $j < $subCount; $j++)
                    {
                        if (isset($CompList[$compsList[$j]->getComponentId()]))
                            $CompList[$compsList[$j]->getComponentId()] += $compsList[$j]->getQty1();
                        else
                            $CompList[$compsList[$j]->getComponentId()] = $compsList[$j]->getQty1();
                    }
                }
            }
            //PURCHASE ORDER
            else 
            {
                $title = "PURCHASE ORDER OF PROJECT";
		//chuan bi de tim kiem
                $fromDate = "$fromyear-". sprintf("%02d", $frommonth). "-00";
                $toDate= "$toyear-". sprintf("%02d", $tomonth). "-99";

                if ($allprojects)
                {
                    //Tim kiem
                    $c = new Criteria();
                    $c->add(POPeer::APPROVALSTATUS  , CommonValues::$gMRApprovalStatus['key']['Approved']);
                    $c->add(POPeer::COMPCATEGORY, CommonValues::$gCompCategory['key']['Main Component']);
                    $c->add(POPeer::RECEIVEDSTATUS, CommonValues::$gPOReceivedStatus['key']['In progress']);
                    $c->addOr(POPeer::RECEIVEDSTATUS, CommonValues::$gPOReceivedStatus['key']['Fully Received']);
                    $c->addOr(POPeer::RECEIVEDSTATUS, CommonValues::$gPOReceivedStatus['key']['Partially Received']);
                    $c->add(POPeer::PROJECT_ID, null, Criteria::ISNOTNULL);
                    $c->add(POPeer::CREATED_AT, $fromDate, Criteria::GREATER_EQUAL);
                    $c->addAnd(POPeer::CREATED_AT, $toDate, Criteria::LESS_EQUAL);
                    $POList = POPeer::doSelect($c);	
                }
                else
                {
                    //Tim kiem
                    $c = new Criteria();
                    $c->add(POPeer::APPROVALSTATUS  , CommonValues::$gMRApprovalStatus['key']['Approved']);
                    $c->add(POPeer::COMPCATEGORY, CommonValues::$gCompCategory['key']['Main Component']);
                    $c->add(POPeer::RECEIVEDSTATUS, CommonValues::$gPOReceivedStatus['key']['In progress']);
                    $c->addOr(POPeer::RECEIVEDSTATUS, CommonValues::$gPOReceivedStatus['key']['Fully Received']);
                    $c->addOr(POPeer::RECEIVEDSTATUS, CommonValues::$gPOReceivedStatus['key']['Partially Received']);
                    $c->add(POPeer::PROJECT_ID, $projectId);
                    $c->add(POPeer::CREATED_AT, $fromDate, Criteria::GREATER_EQUAL);
                    $c->addAnd(POPeer::CREATED_AT, $toDate, Criteria::LESS_EQUAL);
                    $POList = POPeer::doSelect($c);	
                }
		
		
		$CompList= array();
                $count = count($POList);
                for ($i = 0; $i < $count; $i++)
                {
                    $c = new Criteria();
                    $c->add(ComponentsPeer::COMPONENTGROUP_ID, $POList[$i]->getComponentgroupId());
                    $compsList = ComponentsPeer::doSelect($c);
                    $subCount = count($compsList);
                    for ($j = 0; $j < $subCount; $j++)
                    {
                        if (isset($CompList[$compsList[$j]->getComponentId()]))
                            $CompList[$compsList[$j]->getComponentId()] += $compsList[$j]->getQty1();
                        else
                            $CompList[$compsList[$j]->getComponentId()] = $compsList[$j]->getQty1();
                    }
                }	
            }
            
            //Do du lieu thanh mang 2 chieu
            $dem = 1;
            foreach ($CompList as $compId => $value)
            {                
                //$comp = new Component();
                $comp = ComponentPeer::retrieveByPK($compId);
                $result[$dem]['Product Code'] = $comp->getProductcode();
                $result[$dem]['Product Description'] = $comp->getDescription();
                $result[$dem]['Brand'] = $comp->getBrand();
                $result[$dem]['Unit'] = $comp->getComponentUnit();
                $result[$dem]['Qty'] = CommonValues::FormatCurrency($value);
                $dem++;
            }
            
            $this->title = $title;
            $this->result = $result;
            
            if ($this->getRequestParameter('ExportToExcel') && count($result))
            {
                $excel = new ExportExcel("PROJECT_$type-".date('y').'-'.date('m').".xls");
                $excel->BuildData($result, true, false, 'NO');
                $excel->GenerateExcelFile();
            }
	}
	
}

public function executeProvision(){
    CalcTime::Start();
    $this->getResponse()->setTitle('WMS-Report-Provision');

    if ($this->getRequest()->isMethod('post')){

        $filters = $this->getRequestParameter('filters');
        $year = isset($filters['year']) ? $filters['year']: 5;

        $this->filters = $filters;
                
        $c = new Criteria();
        $c->addAscendingOrderByColumn(ComponentPeer::CATEGORY);
        $c->addAscendingOrderByColumn(ComponentPeer::ID);
        $c->addJoin(WarehousePeer::COMPONENT_ID, ComponentPeer::ID, Criteria::LEFT_JOIN);
        $c->add(WarehousePeer::CLOSING, 0, Criteria::GREATER_THAN);
        $WHList = WarehousePeer::doSelect($c);
        
        $count = count($WHList);
        for ($i = 0; $i < $count; $i++)
            $Sign[$WHList[$i]->getId()] = 1;
                
        $startDate = date('Y-m-d', strtotime("-$year years"));
        
        $connection = Propel::getConnection();        
        
        $query = 'SELECT DISTINCT sl_warehouse.id as whId FROM sl_warehouse, sl_components, sl_tallyin
            WHERE sl_warehouse.closing > 0 && sl_warehouse.component_id = sl_components.component_id 
            && sl_components.componentgroup_id = sl_tallyin.componentgroup_id && sl_tallyin.created_at > ?';
        $statement = $connection->prepareStatement($query);
        $statement->setString(1, $startDate);
        $resultset = $statement->executeQuery();
        
        $resultset->next();                    
        while (!$resultset->isAfterLast())
        {   
            $whId = $resultset->get("whId");
            unset($Sign[$whId]);
            $resultset->next();        
        }
        
        $query = 'SELECT DISTINCT sl_warehouse.id as whId FROM sl_warehouse, sl_components, sl_tallyout
            WHERE sl_warehouse.closing > 0 && sl_warehouse.component_id = sl_components.component_id 
            && sl_components.componentgroup_id = sl_tallyout.componentgroup_id && sl_tallyout.created_at > ?';
        $statement = $connection->prepareStatement($query);
        $statement->setString(1, $startDate);
        $resultset = $statement->executeQuery();
        
        $resultset->next();                    
        while (!$resultset->isAfterLast())
        {   
            $whId = $resultset->get("whId");
            unset($Sign[$whId]);
            $resultset->next();        
        }
        $WHIDList = array_keys($Sign);
        $count = count($WHIDList);
        $dem = 1;
        for ($i = 0; $i < $count; $i++)
        {
            $wh = WarehousePeer::retrieveByPK($WHIDList[$i]);
            $comp = $wh->getComponent();
            $result[$dem]['Product Code'] = $comp->getProductcode();
            $result[$dem]['Product Description'] = $comp->getDescription();
            $result[$dem]['Brand'] = $comp->getBrand();
            $result[$dem]['Unit'] = $comp->getComponentUnit();
            
            if ($comp->getCategory()==0)
                $result[$dem]['Category'] = "MAIN";
            elseif ($comp->getCategory()==1)
                $result[$dem]['Category'] = "SUB";
            else
                $result[$dem]['Category'] = "RAW";                    
            
            $result[$dem]['Closing'] = CommonValues::FormatCurrency($wh->getClosing());            
            $dem++;
        }
        $this->result = $result;
        $this->rightAlign = array('Closing'=>1);    
        
        if ($this->getRequestParameter('ExportToExcel'))
        {
            $excel = new ExportExcel("PROVISION_".date('y').'-'.date('m').".xls");
            $excel->BuildData($result, true, false, 'NO');
            $excel->GenerateExcelFile();
        }
    }
    CalcTime::ShowTime();
}
public function executeInventory(){
    $this->getResponse()->setTitle('WMS-Report-Inventory');
    $c = new Criteria();
    $c->addAscendingOrderByColumn(ComponentPeer::CATEGORY);
    $c->addAscendingOrderByColumn(ComponentPeer::ID);
    $c->addJoin(WarehousePeer::COMPONENT_ID, ComponentPeer::ID, Criteria::LEFT_JOIN);
    $WHList = WarehousePeer::doSelect($c);
    
    $result = null;
    $count = count($WHList);
    $dem = 1;
    for ($i = 0; $i < $count; $i++)
    {
//        $wh = new Warehouse();
//        $comp = new Component();
        $wh = $WHList[$i];
        $comp = $wh->getComponent();
        $result[$dem]['Product Code'] = $comp->getProductcode();
        $result[$dem]['Product Description'] = $comp->getDescription();
        $result[$dem]['Brand'] = $comp->getBrand();
        $result[$dem]['Unit'] = $comp->getComponentUnit();
        $result[$dem]['Opening'] = CommonValues::FormatCurrency($wh->getOpening());
        $result[$dem]['Total In'] = CommonValues::FormatCurrency($wh->getTotalin());
        $result[$dem]['Total Out'] = CommonValues::FormatCurrency($wh->getTotalout());
        $result[$dem]['Closing'] = CommonValues::FormatCurrency($wh->getClosing());
        $result[$dem]['Min Stock'] = CommonValues::FormatCurrency($comp->getMinstockqty());
        $result[$dem]['Location'] = $comp->getLocation();
        
        if ($comp->getCategory()==0)
            $result[$dem]['Category'] = "MAIN";
        elseif ($comp->getCategory()==1)
            $result[$dem]['Category'] = "SUB";
        else
            $result[$dem]['Category'] = "RAW";                    
                
        $dem++;
    }    
    $this->result = $result;
    $this->rightAlign = array('Opening'=>1, 'Total In'=>1,'Total Out'=>1,'Closing'=>1,'Min Stock'=>1);    
    
    if ($this->getRequestParameter('ExportToExcel'))
    {
        $excel = new ExportExcel("INVENTORY_".date('y').'-'.date('m').".xls");
        $excel->BuildData($result, true, false, 'NO');
        $excel->GenerateExcelFile();
    }
}

public function executeTallyinOR(){
     $this->getResponse()->setTitle('WMS-Report-Tallyin');
    include('filter.php');    
    $this->local = $this->getRequestParameter('local');
    
    if ($this->getRequest()->isMethod('post'))
    {
        //chuan bi de tim kiem
        $fromDate = "TI-". sprintf("%02d",$fromyear % 100)."-". sprintf("%02d", $frommonth). "-000";
        $toDate= "TI-". sprintf("%02d",$toyear % 100)."-". sprintf("%02d", $tomonth). "-999";

        //tim kiem
        $c = new Criteria();
        $c->add(TallyInPeer::STATUS, CommonValues::$gTLStatus['key']['Published']);
        $c->add(TallyInPeer::TYPE, CommonValues::$gTallyInType['key']['Supplier']);
        $c->add(TallyInPeer::TALLYINCODE, $fromDate, Criteria::GREATER_EQUAL);
        $c->addAnd(TallyInPeer::TALLYINCODE, $toDate, Criteria::LESS_EQUAL);
        if ($this->local) 
            $c->add(POPeer::TYPE , CommonValues::$gPOType['key']['Local']);
        else
            $c->add(POPeer::TYPE , CommonValues::$gPOType['key']['Overseas']);
        $c->add(POPeer::APPROVALSTATUS, CommonValues::$gMRApprovalStatus['key']['Approved']);
        $c->add(POPeer::RECEIVEDSTATUS, CommonValues::$gPOReceivedStatus['key']['Fully Received']);
        $c->addOr(POPeer::RECEIVEDSTATUS, CommonValues::$gPOReceivedStatus['key']['Partially Received']);
        $c->addJoin(TallyInPeer::TALLYNO, POPeer::ID, Criteria::LEFT_JOIN);
        $c->addDescendingOrderByColumn(TallyInPeer::TALLYINCODE);

        $ListTI  = TallyInPeer::doSelect($c);

        $result = null;

        //Do du lieu truoc khi tinh toan
        for ($i = $frommonth; $i<= $tomonths; $i++){
            $year = $fromyear +  intval(($i-1)/12);
            $month = $i % 12;
            if ($month == 0) $month = 12;
            $sMonth = sprintf("%02d", $month);
            $result['Partially']["$year-$sMonth"] = 0;
            $result['Fully']["$year-$sMonth"] = 0;
            $result['Total']["$year-$sMonth"] = 0;
        }
        //thong ke
        foreach ($ListTI as $TI)
        {
            //lay nam va thang
            $TIDate = "20".substr($TI->getTallyincode(), 3, 5);
            $result['Partially']["$TIDate"] += 1;
            $result['Total']["$TIDate"]+=1;

            $PO = POPeer::retrieveByPK($TI->getTallyno());
            if ($PO->getReceivedstatus() == CommonValues::$gPOReceivedStatus['key']['Fully Received']
                    && !isset($Dau[$PO->getId()]))
            {
                $result['Fully']["$TIDate"] += 1;
                $result['Partially']["$TIDate"] -= 1;
                $Dau[$PO->getId()] = 1;
            }
        }


        $this->filters = $filters;
        $this->result = $result;

        if ($this->getRequestParameter('ExportToExcel'))
        {
            $excel = new ExportExcel("TALLYIN_OR_".date('y').'-'.date('m').".xls");
            $excel->BuildData($result, true, true, 'PROJECT');
            $excel->GenerateExcelFile();
        }
    }
}

public function executeTallyoutMat(){
    $this->getResponse()->setTitle('WMS-Report-Tallyout');
    include('filter.php');   
    
    if ($this->getRequest()->isMethod('post'))
    {
        $c = new Criteria();
        $c->addAscendingOrderByColumn(ProjectPeer::SALENO);
        $ListProject = ProjectPeer::doSelect($c);
        
        $resultTemp = null;

        $count = count($ListProject);
        for ($i = 0; $i < $count; $i++)
        {

            for ($j = $frommonth; $j<= $tomonths; $j++)
            {
                $year = $fromyear +  intval(($j-1)/12);
                $month = $j % 12;
                if ($month == 0) $month = 12;
                $sMonth = sprintf("%02d", $month);

                $resultTemp[$ListProject[$i]->__toString()]["$year-$sMonth"] = 0;
            }
        }
        //khoi tao total
        for ($j = $frommonth; $j<= $tomonths; $j++)
        {
            $year = $fromyear +  intval(($j-1)/12);
            $month = $j % 12;
            if ($month == 0) $month = 12;
            $sMonth = sprintf("%02d", $month);

            $resultTemp['TOTAL']["$year-$sMonth"] = 0;
        }

        //chuan bi de tim kiem
        $fromDate = "TO-". sprintf("%02d",$fromyear % 100)."-". sprintf("%02d", $frommonth). "-000";
        $toDate= "TO-". sprintf("%02d",$toyear % 100)."-". sprintf("%02d", $tomonth). "-999";

        //tim kiem
        $c = new Criteria();    
        $c->add(TallyOutPeer::STATUS   , CommonValues::$gTLStatus['key']['Published']);
        $c->add(TallyOutPeer::TYPE  , CommonValues::$gTallyOutType['key']['Project']);
        $c->addOr(TallyOutPeer::TYPE  , CommonValues::$gTallyOutType['key']['Production']);
        $c->add(TallyOutPeer::TALLYOUTCODE, $fromDate, Criteria::GREATER_EQUAL);
        $c->addAnd(TallyOutPeer::TALLYOUTCODE, $toDate, Criteria::LESS_EQUAL);
        $TallyoutList  = TallyOutPeer::doSelect($c);   

        $count = count($TallyoutList);
        for ($i = 0; $i < $count; $i++)
        {
            //lay nam va thang
            $TODate = "20".substr($TallyoutList[$i]->getTallyoutcode(), 3, 5);
            if ($TallyoutList[$i]->getType() == CommonValues::$gTallyOutType['key']['Project'])
            {
                $BOM = BOMPeer::retrieveByPK($TallyoutList[$i]->getTallyno());
                if (!$BOM)                continue;

                $resultTemp[$BOM->getProject()->__toString()][$TODate] += 1;
                $resultTemp['TOTAL'][$TODate] += 1;
            }
            else
            {
                $resultTemp[ProjectPeer::retrieveByPK($TallyoutList[$i]->getTallyno())->__toString()][$TODate] += 1;
                $resultTemp['TOTAL'][$TODate] += 1;
            }
        }
        $keys = array_keys($resultTemp);    
        $result = null;
        foreach ($keys as $key)
        {        
            for ($j = $frommonth; $j<= $tomonths; $j++)
            {
                $year = $fromyear +  intval(($j-1)/12);
                $month = $j % 12;
                if ($month == 0) $month = 12;
                $sMonth = sprintf("%02d", $month);
                if ($resultTemp[$key]["$year-$sMonth"] > 0)
                    break;
            }
            if ($j <= $tomonths)
                $result[$key] = $resultTemp[$key];
        }        

        $this->filters = $filters;
        $this->result = $result;

        if ($this->getRequestParameter('ExportToExcel'))
        {
            $excel = new ExportExcel("TALLYOUT_MAT_".date('y').'-'.date('m').".xls");
            $excel->BuildData($result, true, true, 'PROJECT');
            $excel->GenerateExcelFile();
        }
    }
}


public function executeMrpoType(){
   
    $this->getResponse()->setTitle('WMS-Report-Mrpotype');
    include('filter.php');  
    
    if ($this->getRequest()->isMethod('post'))
    {
        $MRresult=null;
        $POresult = null;

        //Do du lieu truoc khi tinh toan
        for ($i = $frommonth; $i<= $tomonths; $i++){
            $year = $fromyear +  intval(($i-1)/12);
            $month = $i % 12;
            if ($month == 0) $month = 12;
            $sMonth = sprintf("%02d", $month);

            $MRresult['Stock']["$year-$sMonth"] = 0;
            $MRresult['Factory']["$year-$sMonth"] = 0;
            $MRresult['Other']["$year-$sMonth"] = 0;
            $MRresult['Project']["$year-$sMonth"] = 0;
            $MRresult['TOTAL']["$year-$sMonth"] = 0;

            $POresult['Stock']["$year-$sMonth"] = 0;
            $POresult['Factory']["$year-$sMonth"] = 0;
            $POresult['Other']["$year-$sMonth"] = 0;
            $POresult['Project']["$year-$sMonth"] = 0;
            $POresult['TOTAL']["$year-$sMonth"] = 0;
        }   

        //chuan bi de tim kiem
        $fromDate = "$fromyear-". sprintf("%02d", $frommonth). "-00";
        $toDate= "$toyear-". sprintf("%02d", $tomonth). "-99";

        //Tim kiem
        $c = new Criteria();
        $c->add(POPeer::APPROVALSTATUS  , CommonValues::$gMRApprovalStatus['key']['Approved']);
        $c->add(POPeer::RECEIVEDSTATUS, CommonValues::$gPOReceivedStatus['key']['In progress']);
        $c->addOr(POPeer::RECEIVEDSTATUS, CommonValues::$gPOReceivedStatus['key']['Fully Received']);
        $c->addOr(POPeer::RECEIVEDSTATUS, CommonValues::$gPOReceivedStatus['key']['Partially Received']);
        $c->add(POPeer::CREATED_AT, $fromDate, Criteria::GREATER_EQUAL);
        $c->addAnd(POPeer::CREATED_AT, $toDate, Criteria::LESS_EQUAL);
        $POList = POPeer::doSelect($c);

        foreach ($POList as $PO)
        {
            //lay nam va thang
            $PODate = substr($PO->getCreatedAt(), 0, 7);
            $POresult[CommonValues::$gMRCategory['value'][$PO->getCategory()]][$PODate] += 1;
            $POresult['TOTAL'][$PODate] += 1;
        }

        //Tim kiem
        $c = new Criteria();
        $c->add(MRPeer::APPROVALSTATUS , CommonValues::$gMRApprovalStatus['key']['Approved']);
        $c->add(MRPeer::APPROVALSTATUS  , CommonValues::$gMRApprovalStatus['key']['Approved']);
        $c->add(MRPeer::STATUS, CommonValues::$gMRStatus['key']['In progress']);
        $c->addOr(MRPeer::STATUS, CommonValues::$gMRStatus['key']['Completed']);
        $c->add(MRPeer::CREATED_AT, $fromDate, Criteria::GREATER_EQUAL);
        $c->addAnd(MRPeer::CREATED_AT, $toDate, Criteria::LESS_EQUAL);
        $MRList = MRPeer::doSelect($c);

        $c = new Criteria();
        $c->add(MR2Peer::APPROVALSTATUS  , CommonValues::$gMRApprovalStatus['key']['Approved']);
        $c->add(MR2Peer::STATUS, CommonValues::$gMRStatus['key']['In progress']);
        $c->addOr(MR2Peer::STATUS, CommonValues::$gMRStatus['key']['Completed']);
        $c->add(MR2Peer::CREATED_AT, $fromDate, Criteria::GREATER_EQUAL);
        $c->addAnd(MR2Peer::CREATED_AT, $toDate, Criteria::LESS_EQUAL);
        $MRList = array_merge($MRList, MR2Peer::doSelect($c));

        $c = new Criteria();
        $c->add(MR3Peer::APPROVALSTATUS  , CommonValues::$gMRApprovalStatus['key']['Approved']);
        $c->add(MR3Peer::STATUS, CommonValues::$gMRStatus['key']['In progress']);
        $c->addOr(MR3Peer::STATUS, CommonValues::$gMRStatus['key']['Completed']);
        $c->add(MR3Peer::CREATED_AT, $fromDate, Criteria::GREATER_EQUAL);
        $c->addAnd(MR3Peer::CREATED_AT, $toDate, Criteria::LESS_EQUAL);
        $MRList = array_merge($MRList, MR3Peer::doSelect($c));

        foreach ($MRList as $MR)
        {
            //lay nam va thang
            $MRDate = substr($MR->getCreatedAt(), 0, 7);
            $MRresult[CommonValues::$gMRCategory['value'][$MR->getCategory()]][$MRDate] += 1;
            $MRresult['TOTAL'][$MRDate] += 1;
        }

        $this->filters = $filters;
        $this->MRresult = $MRresult;
        $this->POresult = $POresult;

        if ($this->getRequestParameter('ExportToExcel'))
        {
            $excel = new ExportExcel("MRPO_BYTYPE_".date('y').'-'.date('m').".xls");
            $excel->BuildData($MRresult, true, false, 'TYPE');
            $excel->BuildData($POresult, true, false, 'TYPE');
            $excel->GenerateExcelFile();
        }
    }
}




public function executeValueLO(){
   $this->getResponse()->setTitle('WMS-Report-Value-Lo');
    include('filter.php');    
    
    if ($this->getRequest()->isMethod('post'))
    {
        $result=null;
        $resultValue = null;

        //Do du lieu truoc khi tinh toan
        for ($i = $frommonth; $i<= $tomonths; $i++){
            $year = $fromyear +  intval(($i-1)/12);
            $month = $i % 12;
            if ($month == 0) $month = 12;
            $sMonth = sprintf("%02d", $month);
            $result['Local']["$year-$sMonth"] = 0;
            $result['Overseas']["$year-$sMonth"] = 0;
            $result['TOTAL']["$year-$sMonth"] = 0;
            $resultValue['Local']["$year-$sMonth"] = 0;
            $resultValue['Overseas']["$year-$sMonth"] = 0;
            $resultValue['TOTAL']["$year-$sMonth"] = 0;
        }   

        //chuan bi de tim kiem
        $fromDate = "$fromyear-". sprintf("%02d", $frommonth). "-00";
        $toDate= "$toyear-". sprintf("%02d", $tomonth). "-99";

        //Tim kiem
        $c = new Criteria();
        $c->add(POPeer::APPROVALSTATUS  , CommonValues::$gMRApprovalStatus['key']['Approved']);
        $c->add(POPeer::RECEIVEDSTATUS, CommonValues::$gPOReceivedStatus['key']['In progress']);
        $c->addOr(POPeer::RECEIVEDSTATUS, CommonValues::$gPOReceivedStatus['key']['Fully Received']);
        $c->addOr(POPeer::RECEIVEDSTATUS, CommonValues::$gPOReceivedStatus['key']['Partially Received']);
        $c->add(POPeer::CREATED_AT, $fromDate, Criteria::GREATER_EQUAL);
        $c->addAnd(POPeer::CREATED_AT, $toDate, Criteria::LESS_EQUAL);
        $POList = POPeer::doSelect($c);
      
        foreach ($POList as $PO)
        {           
            //lay nam va thang
            $PODate = substr($PO->getCreatedAt(), 0, 7);
            $result[CommonValues::$gPOType['value'][$PO->getType()]][$PODate] += 1;
            $result['TOTAL'][$PODate] += 1;
            $resultValue[CommonValues::$gPOType['value'][$PO->getType()]][$PODate] += $PO->getTotalprice()/CommonValues::RateCurrency($PO->getCurrency());
            $resultValue['TOTAL'][$PODate] += $PO->getTotalprice()/CommonValues::RateCurrency($PO->getCurrency());
        }
        $this->filters = $filters;
        $this->result = $result;
        $this->resultValue = $resultValue;
         if ($this->getRequestParameter('ExportToExcel'))
        {
            $excel = new ExportExcel("VALUE_LO".date('y').'-'.date('m').".xls");
            $excel->BuildData($result, true, false, 'TYPE');
            $excel->BuildData($resultValue, true, false, 'TYPE');
            $excel->GenerateExcelFile();
        }
    }
}



public function executeValuePO(){
    $this->getResponse()->setTitle('WMS-Report-Value-Po');
    include('filter.php');   
    
    if ($this->getRequest()->isMethod('post'))
    {
        $result=null;
        $resultValue = null;

        //Do du lieu truoc khi tinh toan
        for ($i = $frommonth; $i<= $tomonths; $i++){
            $year = $fromyear +  intval(($i-1)/12);
            $month = $i % 12;
            if ($month == 0) $month = 12;
            $sMonth = sprintf("%02d", $month);
            $result['Stock']["$year-$sMonth"] = 0;
            $result['Factory']["$year-$sMonth"] = 0;
            $result['Other']["$year-$sMonth"] = 0;
            $result['Project']["$year-$sMonth"] = 0;
            $result['TOTAL']["$year-$sMonth"] = 0;
        }   

        //chuan bi de tim kiem
        $fromDate = "$fromyear-". sprintf("%02d", $frommonth). "-00";
        $toDate= "$toyear-". sprintf("%02d", $tomonth). "-99";

        //Tim kiem
        $c = new Criteria();
        $c->add(POPeer::APPROVALSTATUS  , CommonValues::$gMRApprovalStatus['key']['Approved']);
        $c->add(POPeer::RECEIVEDSTATUS, CommonValues::$gPOReceivedStatus['key']['In progress']);
        $c->addOr(POPeer::RECEIVEDSTATUS, CommonValues::$gPOReceivedStatus['key']['Fully Received']);
        $c->addOr(POPeer::RECEIVEDSTATUS, CommonValues::$gPOReceivedStatus['key']['Partially Received']);
        $c->add(POPeer::CREATED_AT, $fromDate, Criteria::GREATER_EQUAL);
        $c->addAnd(POPeer::CREATED_AT, $toDate, Criteria::LESS_EQUAL);
        $POList = POPeer::doSelect($c);     
        foreach ($POList as $PO)
        {          
            //lay nam va thang
            $PODate = substr($PO->getCreatedAt(), 0, 7);
            $result[CommonValues::$gMRCategory['value'][$PO->getCategory()]][$PODate] += $PO->getTotalprice()/CommonValues::RateCurrency($PO->getCurrency());
            $result['TOTAL'][$PODate] += $PO->getTotalprice()/CommonValues::RateCurrency($PO->getCurrency());
        }        
        $this->filters = $filters;
        $this->result = $result;
        if ($this->getRequestParameter('ExportToExcel'))
        {
            $excel = new ExportExcel("VALUE_PO".date('y').'-'.date('m').".xls");
            $excel->BuildData($result, true, false, 'TYPE');
            $excel->BuildData($resultValue, true, false, 'TYPE');
            $excel->GenerateExcelFile();
        }  
    }
    
}
public function executeOntimePO(){
     $this->getResponse()->setTitle('WMS-Report-Ontime-Po');
    include('filter.php');   
    
    if ($this->getRequest()->isMethod('post'))
    {
        $result=null;
        $resultValue = null;

        //Do du lieu truoc khi tinh toan
        for ($i = $frommonth; $i<= $tomonths; $i++){
            $year = $fromyear +  intval(($i-1)/12);
            $month = $i % 12;
            if ($month == 0) $month = 12;
            $sMonth = sprintf("%02d", $month);
            $result['LATE(>1day)']["$year-$sMonth"] = 0;
            $result['ONTIME']["$year-$sMonth"] = 0;
            $result['TOTAL']["$year-$sMonth"] = 0;
            $resultValue['LATE(>1day)']["$year-$sMonth"] = 0;
            $resultValue['ONTIME']["$year-$sMonth"] = 0;
            $resultValue['TOTAL']["$year-$sMonth"] = 0;
        }   

        //chuan bi de tim kiem
        $fromDate = "$fromyear-". sprintf("%02d", $frommonth). "-00";
        $toDate= "$toyear-". sprintf("%02d", $tomonth). "-99";

        //Tim kiem
        $c = new Criteria();
        $c->add(POPeer::APPROVALSTATUS  , CommonValues::$gMRApprovalStatus['key']['Approved']);
        $c->add(POPeer::RECEIVEDSTATUS, CommonValues::$gPOReceivedStatus['key']['In progress']);
        $c->addOr(POPeer::RECEIVEDSTATUS, CommonValues::$gPOReceivedStatus['key']['Fully Received']);
        $c->addOr(POPeer::RECEIVEDSTATUS, CommonValues::$gPOReceivedStatus['key']['Partially Received']);
        $c->add(POPeer::CREATED_AT, $fromDate, Criteria::GREATER_EQUAL);
        $c->addAnd(POPeer::CREATED_AT, $toDate, Criteria::LESS_EQUAL);
        $POList = POPeer::doSelect($c);

        foreach ($POList as $PO)
        {
            //lay nam va thang
            $PODate = substr($PO->getCreatedAt(), 0, 7);
           // echo CommonValues::RateCurrency($PO->getCurrency());
            //neu ngay mong doi hang nho hon ngay nhan hang thuc su
            if (strcmp($PO->getExparrdate(), $PO->getReceiveddate()) < 0)
            {
                $result['LATE(>1day)'][$PODate] += 1;
                $resultValue['LATE(>1day)'][$PODate] += $PO->getTotalprice()/CommonValues::RateCurrency($PO->getCurrency());
                
                $result['TOTAL'][$PODate] += 1;
                $resultValue['TOTAL'][$PODate] += $PO->getTotalprice()/CommonValues::RateCurrency($PO->getCurrency());
            }
            else  if ($PO->getReceivedstatus() == CommonValues::$gPOReceivedStatus['key']['Fully Received'])
             {
                $result['ONTIME'][$PODate] += 1;
                $resultValue['ONTIME'][$PODate] +=$PO->getTotalprice()/CommonValues::RateCurrency($PO->getCurrency());
                
                $result['TOTAL'][$PODate] += 1;
                $resultValue['TOTAL'][$PODate] +=$PO->getTotalprice()/CommonValues::RateCurrency($PO->getCurrency());
             }
        }
        $this->filters = $filters;
        $this->result = $result;
        $this->resultValue = $resultValue; 
        if ($this->getRequestParameter('ExportToExcel'))
        {
            $excel = new ExportExcel("PO_Ontime".date('y').'-'.date('m').".xls");
            $excel->BuildData($result, true, false, 'TIME');
            $excel->BuildData($resultValue, true, false, 'TIME');
            $excel->GenerateExcelFile();
        }
    }
}

}
